Why are use triggers in the SQL Server Database?

Question

Ashutosh Kumar Verma · Answer

SQL Server Trigger
Triggers in a database are special stored procedures that are automatically executed or fired in response to certain events in a table or view. It is used to enforce business rules, ensure data integrity, and automate business based on data transformation.
and 
Key Uses of Triggers
Data Validation and Enforcement
Verify that a value in a column meets certain criteria before allowing it to be 
insert or updat. For example, below created trigger is prevented from inserting the negative value into the Salary column,
CREATE TRIGGER ValidateSalary
ON Employees
BEFORE INSERT, UPDATE
AS
BEGIN
   IF EXISTS (SELECT * FROM inserted WHERE Salary andlt; 0)
   BEGIN
       RAISERROR ('Salary cannot be negative.', 16, 1);
       ROLLBACK;
   END
END;
and 
Maintain History
Triggers are also used to automatically maintain a history of records that are deleted from tables.
the below trigger is fires automatically when someone deleted the record from 
Employees table and insert the deleted records into Employees_History table
and 
CREATE TRIGGER ValidateSalary ON Employees
AFTER DELETE
AS
BEGIN
  INSERT INTO Employees_History(EmpName, EmpName, Salary)
  SELECT EmpName, EmpName, Salary FROM deleted
END;
and 
Types of Triggers
BEFORE Triggers- Perform before the triggering event (INSERT, UPDATE, DELETE). Useful for validating or editing data before commissioning.
AFTER Triggers- Perform after the triggering event has occurred. Useful for actions like logging or updating linked tables.
INSTEAD OF Triggers- Replace trigger events with intentional actions. Useful for custom tasks.
and 
Benefits of Using Triggers
Automation- The rules are enforced and tasks are performed without the need for manual intervention.
Consistency- Ensure data integrity and business rules are applied consistently throughout the database.
Auditing- Provide a built-in tool to track changes in data to monitor compliance and correct deficiencies.
and 
and 
Also, Read: What are SQL Stored Procedures in the Database?

interview

Why are use triggers in the SQL Server Database?

Ashutosh Kumar Verma

Can you answer this question?

1 Answers

SQL Server Trigger

Key Uses of Triggers

Types of Triggers

Benefits of Using Triggers

Liked By